﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace Watcher
{
    public partial class ClearDB : Form
    {
        public ClearDB()
        {
            InitializeComponent();
        }

        public const string dbVersion = "V1.3.2";

        // Update to V1.3.2
        private bool UpdateV132()
        {
            string newdbVersion = "V1.3.2";
            bool continueupdate = true;
            try
            {
                OdbcConnection conn = new OdbcConnection(ConStr);
                conn.Open();

                if (conn.State == System.Data.ConnectionState.Open)
                {
                    lock (conn)
                    {

                    //    Logging.Write(Color.Orange, "[WatchMyToon] - Update DB to Version: " + newdbVersion);

                        OdbcCommand create = conn.CreateCommand();

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `nXposition` INT( 5 ) NULL AFTER `nTotalBagSlot`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `nYposition` INT( 5 ) NULL AFTER `nXposition`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "UPDATE `TABVersion` SET `szVersionName` = '" + newdbVersion + "';";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                    }
                }

                conn.Close();

                return true;
            }
            catch (Exception ex)
            {
               // Logging.Write(Color.Red, "[WatchMyToon] - Error while update to version " + newdbVersion + "  --> " + ex.Message);
                return false;
            }
        }

        // Update to V1.3.1
        private bool UpdateV131()
        {
            string newdbVersion = "V1.3.1";
            bool continueupdate = true;
            try
            {
                OdbcConnection conn = new OdbcConnection(ConStr);
                conn.Open();

                if (conn.State == System.Data.ConnectionState.Open)
                {
                    lock (conn)
                    {

                       // Logging.Write(Color.Orange, "[WatchMyToon] - Update DB to Version: " + newdbVersion);

                        OdbcCommand create = conn.CreateCommand();

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `nFreeBagSlot` INT( 5 ) NULL AFTER `bIsInParty`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `nTotalBagSlot` INT( 5 ) NULL AFTER `nFreeBagSlot`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "UPDATE `TABVersion` SET `szVersionName` = '" + newdbVersion + "';";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                    }
                }

                conn.Close();

                return true;
            }
            catch (Exception ex)
            {
               // Logging.Write(Color.Red, "[WatchMyToon] - Error while update to version " + newdbVersion + "  --> " + ex.Message);
                return false;
            }
        }

        // Update to V1.3.0
        private bool UpdateV130()
        {
            string newdbVersion = "V1.3.0";
            bool continueupdate = true;
            try
            {
                OdbcConnection conn = new OdbcConnection(ConStr);
                conn.Open();

                if (conn.State == System.Data.ConnectionState.Open)
                {
                    lock (conn)
                    {
                       // Logging.Write(Color.Orange, "[WatchMyToon] - Update DB to Version: " + newdbVersion);

                        OdbcCommand create = conn.CreateCommand();

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToons` ADD `szClass` VARCHAR( 50 ) NULL AFTER `szRace`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;


                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToons` ADD `szCurrentSpec` VARCHAR( 50 ) NULL AFTER `szClass`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "UPDATE `TABVersion` SET `szVersionName` = '" + newdbVersion + "';";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;
                    }
                }
                else
                    return false;

                conn.Close();

                return true;
            }
            catch (Exception ex)
            {
               // Logging.Write(Color.Red, "[WatchMyToon] - Error while update to version " + newdbVersion + "  --> " + ex.Message);
                return false;
            }
        }

        // Update to V1.2.0
        private bool UpdateV120()
        {
            string newdbVersion = "V1.2.0";
            bool continueupdate = true;
            try
            {
                OdbcConnection conn = new OdbcConnection(ConStr);
                conn.Open();

                if (conn.State == System.Data.ConnectionState.Open)
                {
                    lock (conn)
                    {
                       // Logging.Write(Color.Orange, "[WatchMyToon] - Update DB to Version: " + newdbVersion);

                        OdbcCommand create = conn.CreateCommand();

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABAchievements` (`nToonId` int(15) NOT NULL,`nAchievLfd` int(15) NOT NULL,`szAchievId` varchar(300) NOT NULL,PRIMARY KEY (`nToonId`,`nAchievLfd`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABQuestLog` (`nToonId` int(15) NOT NULL,`nQuestId` int(15) NOT NULL,`szQuestName` varchar(200) NOT NULL,`nQuestLevel` int(5) NOT NULL,`nComplete` int(1) NOT NULL,PRIMARY KEY (`nToonId`,`nQuestId`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {

                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABGroupInvites` (`nToonId` int(15) NOT NULL,`szInviterName` varchar(50) NOT NULL,`szStatus` varchar(50) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {

                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABGuildInvites` (`nToonId` int(15) NOT NULL,`szInviterName` varchar(50) NOT NULL,`szGuildName` varchar(50) NOT NULL,`szStatus` varchar(50) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `bIsInGuild` INT( 1 ) NULL AFTER `szMoneyInCopper`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `bIsInParty` INT( 1 ) NULL AFTER `bIsInGuild`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `szGuildName` VARCHAR( 50 ) NULL AFTER `bIsInGuild`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;


                        if (continueupdate)
                        {
                            try
                            {

                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABEventHandling` (`nToonId` int(15) NOT NULL, `nEventId` int(30) NOT NULL AUTO_INCREMENT,`szEventDesc` varchar(50) NOT NULL,`szEventText` varchar(200) NULL, PRIMARY KEY (`nToonId`,`nEventId`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "UPDATE `TABVersion` SET `szVersionName` = '" + newdbVersion + "';";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        create.Dispose();
                    }

                    conn.Close();
                    return true;
                }
                else
                    return false;


            }
            catch (Exception ex)
            {
                //Logging.Write(Color.Red, "[WatchMyToon] - Error while update to version " + newdbVersion + "  --> " + ex.Message);
                return false;
            }

        }

        // Update to V1.1.0
        private bool UpdateV110()
        {
            string newdbVersion = "V1.1.0";
            bool continueupdate = true;

            try
            {
                OdbcConnection conn = new OdbcConnection(ConStr);
                conn.Open();

                if (conn.State == System.Data.ConnectionState.Open)
                {
                    lock (conn)
                    {
                       // Logging.Write(Color.Orange, "[WatchMyToon] - Update DB to Version: " + newdbVersion);

                        OdbcCommand create = conn.CreateCommand();

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABMessageReceive` (`nMsgId` int(30) NOT NULL AUTO_INCREMENT,`nToonId` int(15) NOT NULL,`szMessageType` varchar(50) NOT NULL,`szMessagePartner` varchar(50) NOT NULL,`szMessageText` varchar(500) NOT NULL,`dtTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`nMsgId`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABMessageToSend` (`nMsgId` int(30) NOT NULL AUTO_INCREMENT,`nToonId` int(15) NOT NULL,`szMessageType` varchar(50) NOT NULL,`szMessagePartner` varchar(50) NOT NULL,`szMessageText` varchar(500) NOT NULL,`dtTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`nMsgId`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABToons` (`nToonId` int(15) NOT NULL AUTO_INCREMENT,`szGender` varchar(50) NOT NULL,`szRace` varchar(50) NOT NULL,`szName` varchar(50) NOT NULL,`dtLastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`nToonId`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABToonsDetail` (`nToonId` int(15) NOT NULL,`nCurrentLvl` int(10) NOT NULL,`nCurrentXp` int(11) NOT NULL,`nNextLvlXp` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToons` ADD `szRealm` VARCHAR( 100 ) NULL AFTER `nToonId`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToons` ADD `szFaction` VARCHAR( 50 ) NULL AFTER `szRealm`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "CREATE TABLE IF NOT EXISTS `TABLoots` (`nToonId` int(15) NOT NULL,`nLootNr` int(15) NOT NULL AUTO_INCREMENT,`szColor` varchar(100) NOT NULL,`szId` varchar(100) NOT NULL, `szName` varchar(200) NOT NULL,`szAmount` varchar(50) NOT NULL,`dtInsert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`nToonId`,`nLootNr`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `szHealth` VARCHAR( 200 ) NULL ,ADD `szZoneId` VARCHAR( 200 ) NULL ,ADD `szSubZone` VARCHAR( 200 ) NULL ,ADD `szZoneText` VARCHAR( 200 ) NULL;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `szPowerType` VARCHAR( 50 ) NULL AFTER `nNextLvlXp` ,ADD `szPower` VARCHAR( 50 ) NULL AFTER `szPowerType`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "ALTER TABLE `TABToonsDetail` ADD `szMoneyInCopper` VARCHAR( 20 ) NULL AFTER `szZoneText`;";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        if (continueupdate)
                        {
                            try
                            {
                                create.CommandText = "UPDATE `TABVersion` SET `szVersionName` = '" + newdbVersion + "';";
                                create.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                continueupdate = false;
                            }
                        }
                        else
                            return false;

                        create.Dispose();

                    }

                    conn.Close();
                    return true;
                }
                else
                    return false;

            }
            catch (Exception ex)
            {
              //  Logging.Write(Color.Red, "[WatchMyToon] - Error while update to version V1.0.0  --> " + ex.Message);
                return false;
            }

        }

        //*****************

        // look for update
        private void VersionControl()
        {
            try
            {

                if (connection.State == System.Data.ConnectionState.Open)
                {
                    lock (connection)
                    {
                        bool tabversionexist = false;
                        try
                        {
                            OdbcCommand check = connection.CreateCommand();
                            check.CommandText = "Select * from TABVersion;";
                            check.ExecuteNonQuery();
                            check.Dispose();

                            tabversionexist = true;
                        }
                        catch (Exception ex)
                        {
                    //        Logging.Write(Color.Orange, "[WatchMyToon] - TabVersion does not exist, creating!");
                            tabversionexist = false;
                        }

                        if (!tabversionexist)
                        {
                            OdbcCommand create = connection.CreateCommand();
                            create.CommandText = "CREATE TABLE IF NOT EXISTS `TABVersion` (`szVersionName` varchar(50) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
                            create.ExecuteNonQuery();
                            create.CommandText = "INSERT INTO `TABVersion` (`szVersionName`) VALUES ('V0.0.0');";
                            create.ExecuteNonQuery();
                            tabversionexist = true;
                        }

                        if (tabversionexist)
                        {
                            bool updtodate = false;

                            while (!updtodate)
                            {
                                OdbcCommand command = new OdbcCommand("SELECT * FROM `TABVersion`;", connection);
                                OdbcDataReader reader = command.ExecuteReader();

                                while (reader.Read())
                                {
                                    string currentVersion = reader["szVersionName"].ToString();

                                    switch (currentVersion)
                                    {
                                        case "V0.0.0":
                                            if (UpdateV110())
                                                progressBar1.PerformStep();
                                            //       Logging.Write(Color.Green, "[WatchMyToon] - Update done");
                                            else
                                            {
                                                //        Logging.Write(Color.Red, "[WatchMyToon] -Error while update");
                                                //         doWork = false;
                                                return;
                                            }
                                            break;
                                        case "V1.1.0":
                                            if (UpdateV120())
                                                progressBar1.PerformStep();
                                            else
                                            {
                                               // Logging.Write(Color.Red, "[WatchMyToon] -Error while update");
                                             //   doWork = false;
                                                return;
                                            }
                                            break;
                                        case "V1.2.0":
                                            if (UpdateV130())
                                                progressBar1.PerformStep();
                                            else
                                            {
                                               // Logging.Write(Color.Red, "[WatchMyToon] -Error while update");
                                              //  doWork = false;
                                                return;
                                            }
                                            break;
                                        case "V1.3.0":
                                            if (UpdateV131())
                                                progressBar1.PerformStep();
                                            else
                                            {
                                               // Logging.Write(Color.Red, "[WatchMyToon] -Error while update");
                                              //  doWork = false;
                                                return;
                                            }
                                            break;
                                        case "V1.3.1":
                                            if (UpdateV132())
                                                progressBar1.PerformStep();     
                                            else
                                            {
                                               
                                                return;
                                            }
                                            break;
                                        case dbVersion:
                                           
                                            updtodate = true;
                                            break;

                                    }

                                }

                                reader.Close();
                                reader.Dispose();
                                command.Dispose();

                            }


                        }


                    }
                }
            }
            catch (Exception ex)
            {
               // Logging.Write(Color.Red, "[WatchMyToon] - Error while VersionControl  --> " + ex.Message);
            }
        }

        private string ConStr = "DSN=WatchMyToon_DB";
        private OdbcConnection connection;

        private void button1_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure to recreate the DB ?", "Question", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
            {
                try
                {
                    progressBar1.Minimum = 0;
                    progressBar1.Maximum = 100;

                    progressBar1.Step = 10;

                    connection = new OdbcConnection(ConStr);
                    connection.Open();

                    progressBar1.PerformStep();
                    string dbname = connection.Database;

                    OdbcCommand drop = connection.CreateCommand();
                    drop.CommandText = "DROP DATABASE " + dbname;
                    progressBar1.PerformStep();

                    drop.ExecuteNonQuery();
                    drop.CommandText = "CREATE DATABASE " + dbname;
                    progressBar1.PerformStep();

                    drop.ExecuteNonQuery();
                    progressBar1.PerformStep();

                    connection.Close();
                    connection.Open();
                    VersionControl();

                    progressBar1.PerformStep();

                    connection.Close();

                    MessageBox.Show("Database recreated");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
    }
}
